package com.info.zhiduoduo.repository.mapper.maker;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.GetPhoneDto;
import com.info.zhiduoduo.repository.dto.maker.TemporarilyNotHandledDto;
import com.info.zhiduoduo.repository.dto.maker.VerifyingMakerDto;
import com.info.zhiduoduo.repository.entity.maker.Maker;
import com.info.zhiduoduo.repository.entity.maker.MakerAccount;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author TerryJ
 * @since 2018-12-08
 */
public interface MakerAccountMapper extends BaseMapper<MakerAccount> {

    @Select("select * from zdd_maker_account where userid = #{userid}")
    MakerAccount getUserAccountByUserId(@Param("userid") int userid);

    @Select(" SELECT u.userid,u.invite_code,u.phone,u.bindcompid,c.compname as bindcompname,u.compremark," +
            " u.has_company,u.has_contract,u.work_company,u.notice_flag," +
            " u.usertype,u.maker_type,u.issue_date,u.expire_date,u.payid,tp.state as paidState," +
            " us.tradecategoryid,tc.tradecategory,ua.zdd_no_audit_reason,us.crowdsource_convert_zdd_maker," +
            " ua.acctid,ua.realname,ua.identity_type,ua.idno,ua.individual_account_number,ua.opening_location, ua.gong_qing_data_submint,  " +
            " ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason," +
            " ua.industry,ua.occupation,ua.idfrontimg,ua.idbackimg,ua.educationimg,ua.identificationimg," +
            " ua.realname_auth as realnameAuth,ua.createtime,ua.updatetime,ua.government_examine_state," +
            " ua.government_rejection_reasons,ua.business_license_no,ua.business_license_pic,ua.electronic_signature," +
            " ua.gong_qing_detection_failure,ua.gong_qing_detection_failure_count,ua.gong_qing_detection_failure_reason," +
            " ua.reject_business_register_reason,ua.business_data_submint,ua.examine_business_data_state,ua.temporary_login, " +
            " ub.cardtype,ub.bankname,ub.payername,ub.acctno,ub.telephone,pa.realname as inviter," +
            " uar.reason as authReason,uar.remark as authRemark " +
            " FROM zdd_maker_account ua " +
            " JOIN zdd_maker u on u.userid=ua.userid " +
            " LEFT JOIN zdd_company c on c.compid=u.bindcompid " +
            " LEFT JOIN zdd_maker_studio us on us.userid=ua.userid " +
            " LEFT JOIN zdd_maker_bankcard ub on ub.userid=ua.userid and ub.state=1 and ub.accttype = 1 " +
            " LEFT JOIN (SELECT acctid,remark,reason,state from zdd_maker_auth_record GROUP BY acctid ORDER BY id desc)as uar on uar.acctid = ua.acctid" +
            " LEFT JOIN zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
            " LEFT JOIN zdd_taxer_payment tp on tp.userid = ua.userid and tp.type = 1 and tp.deleted = 0 " +
            " LEFT JOIN zdd_system_tradecategory tc on tc.tradecategoryid=us.tradecategoryid " +
            " WHERE IF(#{state} = 0, 1=1, ua.realname_auth = #{state}) " +
            " AND ua.temporarily_not_handled = 0 " +
            " AND IF(#{state} = 4, ua.business_authentication_state != 1, 1=1) " +
            " AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
//            " and u.maker_type = #{makerType} " +
            " AND IFNULL(ua.createtime, u.createtime) BETWEEN #{startDate} AND #{endDate} " +
            " AND IFNULL(ua.realname,'') like '%${name}%' " +
            " AND IFNULL(c.compname,'') like '%${bindcompname}%' " +
            " AND IFNULL(u.phone,'') like '%${phone}%' " +
            " AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
            " AND IF(ISNULL(#{openingLocation}) , 1=1 , ua.opening_location = #{openingLocation}  ) " +
            " AND IF(ISNULL(#{temporaryLogin}), 1=1, ua.temporary_login = #{temporaryLogin})" +
            " ${dockingCity} "+
            " ORDER BY ua.acctid DESC ")
    List<VerifyingMakerDto> getVerifyList(@Param("makerType") int makerType, @Param("name") String name, @Param("inviter") String inviter,
                                          @Param("state") int state, @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                          @Param("bindcompname") String bindcompname, @Param("phone") String phone,
                                          @Param("openingLocation") Integer openingLocation, @Param("dockingCity") String dockingCity,
                                          @Param("temporaryLogin") Integer temporaryLogin);

    @Select(" SELECT u.userid,u.invite_code,u.phone,u.bindcompid,c.compname as bindcompname,u.compremark," +
            " u.has_company,u.has_contract,u.work_company,u.notice_flag," +
            " u.usertype,u.maker_type,u.issue_date,u.expire_date,u.payid,tp.state as paidState," +
            " us.tradecategoryid,tc.tradecategory,us.crowdsource_convert_zdd_maker," +
            " ua.acctid,ua.realname,ua.identity_type,ua.idno,ua.individual_account_number,ua.opening_location, " +
            " ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason," +
            " ua.industry,ua.occupation,ua.idfrontimg,ua.idbackimg,ua.educationimg,ua.identificationimg," +
            " ua.realname_auth as realnameAuth,ua.createtime,ua.updatetime,ua.temporary_login,ua.electronic_signature," +
            " ua.gong_qing_detection_failure,ua.gong_qing_detection_failure_count,ua.gong_qing_detection_failure_reason," +
            " ub.cardtype,ub.bankname,ub.payername,ub.acctno,ub.telephone,pa.realname as inviter," +
            " (select uar.remark from zdd_maker_auth_record uar " +
            " where uar.acctid = ua.acctid and uar.state = 0 order by uar.id limit 1) as authRemark " +
            " FROM zdd_maker_account ua " +
            " JOIN zdd_maker u on u.userid=ua.userid " +
            " LEFT JOIN zdd_company c on c.compid=u.bindcompid " +
            " LEFT JOIN zdd_maker_studio us on us.userid=ua.userid " +
            " LEFT JOIN zdd_maker_bankcard ub on ub.userid=ua.userid and ub.state=1 and ub.accttype = 1 " +
            " LEFT JOIN zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
            " LEFT JOIN zdd_taxer_payment tp on tp.userid = ua.userid and tp.type = 1 and tp.deleted = 0 " +
            " LEFT JOIN zdd_system_tradecategory tc on tc.tradecategoryid=us.tradecategoryid " +
            " WHERE ua.realname_auth = 4 " +
            " AND ua.temporarily_not_handled = 0 " +
            " and ua.business_authentication_state = 1 " +
            " AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
//            " and u.maker_type = #{makerType} " +
            " AND IFNULL(ua.createtime, u.createtime) BETWEEN #{startDate} AND #{endDate} " +
            " AND IFNULL(ua.realname,'') like '%${name}%' " +
            " AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
            " AND IF( ISNULL(#{openingLocation}) , 1=1 , ua.opening_location = #{openingLocation}  ) " +
            " AND IF(ISNULL(#{temporaryLogin}), 1=1, ua.temporary_login = #{temporaryLogin})" +
            " ORDER BY ua.acctid DESC ")
    List<VerifyingMakerDto> getBusinessAuthenticationList(@Param("makerType") int makerType, @Param("name") String name, @Param("inviter") String inviter, @Param("startDate") Date startDate,
                                                          @Param("endDate") Date endDate, @Param("openingLocation") Integer openingLocation,
                                                          @Param("temporaryLogin") Integer temporaryLogin);


    @Select(" SELECT u.userid,u.invite_code,u.phone,u.bindcompid,c.compname as bindcompname,u.compremark,ua.expert_label_state,ua.temporarily_not_handled, " +
            " u.has_company,u.has_contract,u.work_company,u.notice_flag," +
            " u.usertype,u.maker_type,u.issue_date,u.expire_date,u.payid,tp.state as paidState," +
            " us.tradecategoryid,tc.tradecategory,us.crowdsource_convert_zdd_maker," +
            " ua.education,ua.political,ua.email,ua.residence,ua.acctid,ua.realname,ua.identity_type,ua.idno," +
            " ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason," +
            " ua.industry,ua.occupation,ua.idfrontimg,ua.idbackimg,ua.educationimg,ua.identificationimg,ua.electronic_signature," +
            " ua.is_registered_studio,ua.phone_of_emergency_contact,ua.name_of_emergency_contact," +
            " ua.realname_auth as realnameAuth,ua.createtime,ua.updatetime," +
            " ua.gong_qing_detection_failure,ua.gong_qing_detection_failure_count,ua.gong_qing_detection_failure_reason," +
            " ub.cardtype,ub.bankname,ub.payername,ub.acctno,ub.telephone,pa.realname as inviter," +
            " (select uar.remark from zdd_maker_auth_record uar " +
            " where uar.acctid = ua.acctid and uar.state = 0 order by uar.id limit 1) as authRemark," +
            " ub.open_acct_bank_name  AS openAcctBankName,ub.alipay_account,ub.payment_account_status " +
            " FROM zdd_maker_account ua " +
            " JOIN zdd_maker u on u.userid=ua.userid " +
            " LEFT JOIN zdd_company c on c.compid=u.bindcompid " +
            " LEFT JOIN zdd_maker_studio us on us.userid=ua.userid " +
            " LEFT JOIN zdd_maker_bankcard ub on ub.userid=ua.userid and ub.state=1 and ub.accttype = 1 " +
            " LEFT JOIN zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
            " LEFT JOIN zdd_taxer_payment tp on tp.userid = ua.userid and tp.type = 1 and tp.deleted = 0 " +
            " LEFT JOIN zdd_system_tradecategory tc on tc.tradecategoryid=us.tradecategoryid " +
            " WHERE ua.acctid = #{acctid}")
    VerifyingMakerDto getVerifyingDetail(@Param("acctid") int acctid);

    /**
     * 需要查看政府审核状态的创客
     * @return
     */
    @Select(" SELECT ua.* FROM zdd_maker_account ua " +
            " WHERE " +
            " ua.realname_auth = 4 " +
            " AND ua.opening_location = 2 " +
            " AND ua.government_examine_state != 1 " +
            " AND ua.individual_account_number IS NOT NULL " +
            " AND ua.individual_account_number != '' " +
            " ORDER BY " +
            " ua.acctid DESC")
    List<MakerAccount> getNeedGovernmentAudit();

    @Update(" update zdd_maker_account set gong_qing_detection_failure_count = #{count}, gong_qing_detection_failure_reason = #{msg}" +
            " where userid = #{userid}")
    int updateMakerDetectionFailureCount(@Param("userid") int userid, @Param("count") int count, @Param("msg") String msg);

    @Select(" SELECT * FROM zdd_maker m " +
            " LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid  " +
            " WHERE m.state = 2 " +
            " AND ma.opening_location = 2  " +
            " AND ma.business_data_submint = 0 " +
            " AND ma.gong_qing_data_submint = 0 " +
            " AND ma.government_examine_state = '' " +
            " AND ma.number_of_remind_to_submit_data < 3 " +
            " AND m.maker_type in (1,3)")
    List<Maker> reminderToSubmitData();

    @Select(" SELECT u.userid,u.phone,u.bindcompid,c.compname as companyName,u.maker_type," +
            " ua.realname,ua.opening_location, " +
            " ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason," +
            " ua.realname_auth as realnameAuth,ua.createtime,ua.updatetime,ua.temporarily_not_handled,ua.not_handled_reason,ua.not_handled_voucher" +
            " FROM zdd_maker_account ua " +
            " LEFT JOIN zdd_maker u ON u.userid=ua.userid " +
            " LEFT JOIN zdd_company c ON c.compid=u.bindcompid " +
            " WHERE " +
            " ua.temporarily_not_handled = 1 " +
            " AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
            " AND IF(ISNULL(#{compname}) OR #{compname}='',1=1,c.compname like '%${compname}%') " +
            " AND IFNULL(ua.createtime, u.createtime) BETWEEN #{startDate} AND #{endDate} " +
            " AND IFNULL(ua.realname,'') like '%${name}%' " +
            " AND IF(ISNULL(#{dockingCity}) OR #{dockingCity} = '',1=1,ua.opening_location in(#{dockingCity}))" +
            " ORDER BY ua.acctid DESC ")
    List<TemporarilyNotHandledDto> getTemporarilyNotHandledList(@Param("makerType") int makerType, @Param("name") String name,
                                                                @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                                                @Param("compname")String compname,@Param("dockingCity")String dockingCity);


    @Select("SELECT m.phone,ma.idno " +
            "FROM zdd_maker m " +
            "LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid " +
            "WHERE m.userid = #{userId} ")
    GetPhoneDto getPhone(@Param("userId") Integer userId);

    @Select(" SELECT ma.*  " +
            " FROM `zdd_maker` m " +
            " LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid " +
            " WHERE m.bindcompid = #{companyId} ")
    List<MakerAccount> getMakerIdno(@Param("companyId") int companyId);
}
